Parameters are the placeholders within the SQL statement that are replaced with values chosen during the recipient definition of the job. By using parameters, the data administrator can use the same basic SQL statement for many jobs, turning over the details to the end user, who can use them without having to know anything about the actual SQL that lies behind the target group, or even know SQL in general. This saves time and effort for all team members. It also allows group members to share the same target groups, while permitting each target group to create different recipient definitions for individual jobs.
A SQL statement for a target group can be a "fixed" statement, or a parameterized statement. A fixed statement contains SQL code that will be used in the same form every time it is used with the target group. For example:
A fixed statement will always yield the same results regardless of the circumstances of how the target group is used (as long as the actual database content does not change). A fixed statement is useful in certain circumstances. The first example given here would simply select all entries from a certain table. If that is the required behavior, then a fixed statement is the correct statement type to use.
The second example selects only those recipients that are in their thirties
(age >= 30 and <= 39). This is very limiting. Targeting a different age group would necessitate creating a new target group with a different SQL statement. In fact, with fixed statements, every different age group would require its own target group and SQL statement. Setting up these fixed statements would involve a lot of work for the data administrator, as well as take away flexibility for the end users.
Using a parameterized statement can save time and effort as well as give end users more flexibility in selecting recipients. A parameterized statement contains placeholders in the SQL code that are replaced by the end user, who uses the target group for the actual recipient definition. These placeholders are not "real" SQL code, but are a sort of "meta" code. By using placeholders, parts of the SQL statement that are not yet "known" can be defined at the moment the SQL statement is entered into the system. These placeholders are then replaced with actual values before the statement is executed.
Continuing with the example above, using placeholders makes it possible to create a parameterized SQL statement that selects all recipients of a certain age range. The end user, who employs the target group in a recipient definition, is left with the decision of what age range to use when creating the recipients definition. This example shows how the actual age range values are replaced with placeholders "
{{from}}" and "
{{to}}":
select email, name, city from recipients where age >= {{from}} and age <= {{to}}
A placeholder is any string of characters that appears between special opening and closing tags. By default, the opening tag is "{{" and the closing tag is "}}". Everything surrounded by these two tags will be treated as parameter placeholders, and not as part of the actual SQL statement. Different tags can be defined if the default tag strings are used somewhere else in the statement and therefore cannot be recognized as placeholder tags. Enter tags in the
Opening Tag and
Closing Tag fields below the
SQL statement field on the Source screen.
|
•
|
Any occurrences of the quote character itself in the value will automatically be escaped. For example, a placeholder is defined as " lastname = '{{name}}'" and a last name of O'Brian is entered as the parameter value (value of " O'Brian"). After replacement, the resulting data would automatically become " lastname = 'O''Brian'" (or whatever escape of the quote character is the correct one for the database in use). Do not define values with quote characters already escaped, since that would lead to a double-escaped character.
|
To parameterize this, use a placeholder instead of this comma separated list, as with "
value in ({{arg}})" or in the quoted form "
value in ('{{arg}}')". Both forms are very similar. The placeholder will be replaced with a comma separated list representing all choices the user makes. Do not include a whole list of placeholders, but only a single placeholder. This single placeholder is then replaced by the list of the choices the user makes.
This parameter type appears as a checkbox on the end user's screen. The checkbox has two associated values – checked or unchecked. Enter these values accordingly – they need to be different values.
Depending on the input from the user (if the box is checked or not), one or the other value will be used directly to replace the parameter placeholder. If the parameter placeholders were not quoted, then only integers can be entered as the two values. If the parameter placeholders were quoted, then any string can be entered for the values, and all occurrences of the quote character in these will be escaped automatically before the replacement. Remember do not escape quotes when entering the values.
The input from the end user will be used directly to replace the parameter placeholder. If the parameter placeholder was quoted, then all occurrences of the quote character in the user input will be automatically escaped before the replacement.
There are two versions of the Edit Field parameter type – single value and multiple values. In the single value version, the input field will be a one line input field, and the value entered by the end user will be the value used to replace the placeholder. This type is only available if the matching placeholder is not inside of an in-clause context. In the multiple values version, the input field will be rendered with multiple lines, and the end user may enter several lines of text. Each line will be interpreted as a separate value, and the placeholder will be replaced with a comma separated list of all values (of all lines). Empty lines will be ignored. This type is only available if the matching placeholder is within an in-clause context.
This parameter type appears as a selection list with multiple entries on the end user's screen. If the parameter is within an in-clause, the list is rendered as a multi-line list field, and the end user may select multiple entries by holding down the
SHIFT or
CTRL key. Otherwise, the list is rendered as a drop-down list and the end user may select only a single entry.
For parameters where LISTSERV Maestro selects from a database, the entries in the selection list can be specified in two ways. They can be specified manually on this wizard screen or a SQL statement can specify them. The SQL statement will then be used to retrieve the values that will appear in the list from the database. Select an option to choose between the two methods. For parameters where LISTSERV selects from a database, all entries must be specified manually on this wizard screen.
Each value consists of two parts, the visible text in the selection list (that is the text that the end user actually sees in the list), and the invisible value associated with that entry. This invisible value, associated with the entry selected by the end user, will be used directly to replace the parameter placeholder. If the parameter placeholder was not quoted, then only integers can be used as the internal value of each entry. If the parameter placeholder was quoted, then any string can be entered for the internal values, and all occurrences of the quote character in these will be escaped automatically before the replacement. Do not escape quotes when entering the values.
To add a new entry, click on the New link to the right of the list. Enter the visible text into the left edit fields, and the internal value into the right field. Click on
[Save Entry]. The new entry will be added to the list.
To modify an existing entry, simply select the entry in the list, edit its visible text and/or internal value in the two edit fields above, and then click
[Save Entry] to save the changes. The entry will be updated accordingly.
To change the ordering of the entries, select the entry to move and click Up or
Down to move it in the list. To delete an entry, select it, and then click
Delete.
Enter a SQL statement. This statement will be executed with the same connection parameters that were specified on the Source screen of the wizard. The result set retrieved will be used to populate the selection list. The values from the first column of the result set will be used as the visible text of the selection list entries. If there is a second column in the result set, then its values will be used as the internal values for the entries. If there is no second column, then the values from the first column will be used both for the visible text and the internal values. Any further columns in the result set are ignored.
Make sure that the internal values match the quote context of the parameter. If the parameter placeholder is not quoted, then the internal values must be integer values (that is the values from the second result set column, or the ones from the first column, if there is only one column). If the values are not integers, then the parameter placeholder must be quoted (any necessary escaping of quotes in the values will happen automatically).
This option is very useful if it is not possible or desirable to enter all the selection values by hand. There may be many different selections, and some of the values may not yet be known. For example, think of a target group that has the city where a recipient lives as one of the parameters in order to do mailings limited to the residents of a certain city:
If this parameter is assigned the type "Selection List" all possible cities could be entered manually. But, this approach requires a lot of work, and all the possible cities may not yet be known. Another drawback to using this approach is that the list would have to be updated manually each time a recipient from a new city is entered into the database. To avoid all this time and effort, use an SQL statement like:
This statement accesses the same table as the target group itself (see the first SQL statement above) using the same database connection settings. It generates exactly one column that contains all cities that are currently in the city column in the table, in alphabetical order. The end user can then simply select one of these cities.
Important: The list may only have a maximum of 100 entries, in order to not overburden the user interface and to protect against abuse. If more entries than this is required, it would probably be better to use the "Edit Field" type for this parameter, and let the end user input the value manually, instead of selecting it from a list with too many entries.
This parameter type appears as an input box on the end user's screen. Choose selections for
Date Input Format and/or
Time Input Format. Choose at least one or choose both. The selection determines whether the user will be asked to input a date, a time, or both. It also determines how the input fields will be arranged. Input fields will be rendered as three input fields for the date (day, month, year order, according to specification), and/or as two or three input fields for the time (hours and minutes with or without seconds, according to specification).
All end user time input must be in the 24h format from 00:00:00 to 23:59:59. An AM/PM input format is not available. In addition to defining how the date/time input will look for the end user, it is also necessary to define how the input from the user is converted into a string that matches the date/time format used in the database. Do this by entering a format string into the specified edit field.
In that format string, use any desired characters. For example, separation characters like "
:" or "
,". Also, use any of the format placeholders listed to the right of the field. Each format placeholder will later be replaced with the corresponding date/time value, in the corresponding format.
|
•
|
year4 – Will be replaced with a four digit representation of the year value entered by the end user (for example "2002"). Available only if a date input format was selected.
|
|
•
|
year2 – Will be replaced with a two digit representation of the year value entered by the end user (for example "02"). Available only if a date input format was selected.
|
|
•
|
month2 – Will be replaced with a two digit representation of the month value entered by the end user (for example "09" or "12"). Available only if a date input format was selected.
|
|
•
|
month1 – Will be replaced with a one or two digit representation of the month value entered by the end user, with months January to September as one digit and months October to December as two digits (for example "9" or "12"). Available only if a date input format was selected.
|
|
•
|
day2 – Will be replaced with a two digit representation of the day value entered by the end user (for example "01" or "31"). Available only if a date input format was selected.
|
|
•
|
day1 – Will be replaced with a one or two digit representation of the day value entered by the end user, with days 1 to 9 as one digit and days 10 to 31 as two digits (for example "1" or "31"). Available only if a date input format was selected.
|
|
•
|
hour2 – Will be replaced with a two digit representation of the hour value entered by the end user (for example "08" or "23"). Available only if a time input format was selected.
|
|
•
|
hour1 – Will be replaced with a one or two digit representation of the hour value entered by the end user, with hours 0 to 9 as one digit and hours 10 to 23 as two digits (for example "1" or "23"). Available only if a time input format was selected.
|
|
•
|
min2 – Will be replaced with a two digit representation of the minute value entered by the end user (for example "04" or "59"). Available only if a time input format was selected.
|
|
•
|
min1 – Will be replaced with a one or two digit representation of the minute value entered by the end user, with minutes 0 to 9 as one digit and minutes 10 to 59 as two digits (for example "4" or "59"). Available only if a time input format was selected.
|
|
•
|
sec2 – Will be replaced with a two digit representation of the seconds value entered by the end user (for example "06" or "59"). Available only if the time input format with seconds was selected.
|
|
•
|
sec1 – Will be replaced with a one or two digit representation of the seconds value entered by the end user, with seconds 0 to 9 as one digit and seconds 10 to 59 as two digits (for example "6" or "59"). Available only if the time input format with seconds was selected.
|
While the format string is being typed into the input field, the sample date/time "Sep. 1, 2002 08:04:06 AM" will be converted into that format and the result displayed below the input field. For example, if "month2/day2/year4 - [hour2:min2:sec2]"is entered as the format, then the sample will display:
The input from the end user will be applied to the format string entered in the same way as with the sample date, and the resulting string will be used to replace the parameter placeholder. All occurrences of the quote character in the date/time string will be escaped before the replacement.
Important: This database format always requires a 24h time format from 00:00:00 to 23:59:59. This input type cannot be used to generate a database time format that includes AM/PM information with hours from 1 to 12. It is also not possible to generate a database date format where the month or day of the week is given in long text, like "Monday, December 2nd, 2003". If such a time format is necessary, use the Edit Field type instead, and let the end user input the date and time manually in the format required by the database (use the parameter's description field to tell end users which format they need to use to be compatible with the database).
